package org.csu.mypetstore.persistence.impl;

import org.csu.mypetstore.domain.Category;
import org.csu.mypetstore.domain.LongCart;
import org.csu.mypetstore.persistence.CategoryDAO;
import org.csu.mypetstore.persistence.DBUtil;
import org.csu.mypetstore.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by songtie on 2015/4/21.
 */
public class CategoryDAOImpl implements CategoryDAO
{
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    private static final String getCategoryListString = "SELECT CATID AS categoryId,NAME,DESCN AS description FROM CATEGORY";
    private static final String getCategoryString = "SELECT CATID AS categoryId,NAME,DESCN AS description FROM CATEGORY WHERE CATID=?";

    @Override
    public List<Category> getCategoryList() {
        List<Category> result = new ArrayList<Category> ();
        try {
            Connection conn = DBUtil.getConnection();
            PreparedStatement statement = conn.prepareStatement(getCategoryListString);
            ResultSet resultSet = statement.executeQuery(getCategoryListString);

            while(resultSet.next())
            {
                Category category = new Category();
                category.setCategoryId(resultSet.getString(1));
                category.setName(resultSet.getString(2));
                category.setDescription(resultSet.getString(3));
                result.add(category);
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public Category getCategory(String categoryId) {
        Category category = null;
        try {
            Connection conn = DBUtil.getConnection();
            PreparedStatement pStatement = conn.prepareStatement(getCategoryString);
            pStatement.setString(1, categoryId);
            ResultSet resultSet = pStatement.executeQuery();
            if(resultSet.next())
            {
                category = new Category();
                category.setCategoryId(resultSet.getString(1));
                category.setName(resultSet.getString(2));
                category.setDescription(resultSet.getString(3));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(pStatement);
            DBUtil.closeConnection(conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return category;
    }

    @Override
    public List<LongCart> findinlongcart(String username) {
        String sql = "select * from longcart where username=? ";
        return template.query(sql,new BeanPropertyRowMapper<LongCart>(LongCart.class),username);
    }

    @Override
    public void incrementQuantityByItemId(String username, String workingItemId) {
        String sql = "update longcart set quantity=quantity+1 where username=? and itemID=?";
        template.update(sql,username,workingItemId);
    }

    @Override
    public void insertlongcart(String username, String workingItemId) {
        String sql = "insert into longcart values(?,?,1)";
        template.update(sql,workingItemId,username);
    }

    @Override
    public void updatecartquantities(String itemId, int quantity, String username) {
        String sql = "update longcart set quantity=? where username=? and itemID=?";
        template.update(sql,quantity,username,itemId);
    }

    @Override
    public void removecartitem(String itemId, int quantity, String username) {
        String sql = "delete from longcart where username=? and itemID=?";
        template.update(sql,username,itemId);
    }

    @Override
    public void clearcart(String username) {
        String sql = "delete from longcart where username=?";
        template.update(sql,username);
    }

}
